package com.GamersCom.db;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.GamersCom.util.GlobalCONST;
import com.GamersCom.util.Util;

/**
 * Executes CRUD operations on the user table
 * @author Frank Sun
 * 
 */

public class UserDAO
{
	protected ConnMgr cm;
	protected Connection conn;
	protected Statement stmt;
	protected QueryMgr qm;
	
	/**
	 * Constructor. Initialize the QueryMgr()
	 */
	public UserDAO()
	{
		super();
		qm = new QueryMgr();
	}

	/**
	 * Insert a user into User Table
	 * @param user
	 * @param paramList
	 */
	public void insertUser(String query, List paramList) throws SQLException, ClassNotFoundException
	{
		qm.executePreparedQuery(query, paramList);
						
	}

	/**
	 * Update a user in the User Table
	 * @param query
	 * @param paramList
	 */
	public void updateUser(String query, List paramList)
	{
		qm.executePreparedQuery(query, paramList);
	}
	
	/**
	 * Delete a user in the User Table
	 * @param query
	 * @param paramList
	 */
	public void deleteUser(String query, List paramList)
	{
		qm.executePreparedQuery(query, paramList);
	}
	
	/**
	 * Get all records from users table
	 * @return List<User>
	 */
	public List<User> getUsers(String query)
	{		
		ResultSet rs = null;
		List<User> userList = new ArrayList<User>();

		try
		{
			 rs = qm.fetchList(query);
			 //validate if the resultSet contains any row
			 if ( ! rs.isBeforeFirst() ) 
			 {
				 return null;
			 }
			 
			while (rs.next())
			{
				User user = new User();
				user.setId(rs.getInt("id"));
				user.setUsername(rs.getString("username"));
				user.setFirstName(rs.getString("firstname"));
				user.setLastName(rs.getString("lastname"));
				user.setEmail(rs.getString("email"));
				user.setPassword(rs.getString("password"));
				user.setAccessLevel(rs.getInt("accesslevel"));
				user.setStatus(rs.getString("status"));
				user.setToken(rs.getString("token"));
				user.setStartDate(rs.getDate("startDate"));
				user.setSecurityQuestion(rs.getString("securityQuestion"));
				user.setAnswer(rs.getString("answer"));
				userList.add(user);
			}
		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}

		return userList;
	}

	
	/**
	 * Get a user with the param specified 
	 * @param query
	 * @param paramList
	 * @return User
	 */
	public User getUser(String query, List paramList)
	{
		ResultSet rs = null;
		User user = new User();

		try
		{
			rs = qm.fetchList(query, paramList);	
			
			if ( ! rs.first() )  //no record found
			{
				System.out.println(query);
				System.out.println("no record found");
				return null;
			}
			else 
			{								
				user.setId(rs.getInt("id"));
				user.setUsername(rs.getString("username"));
				user.setFirstName(rs.getString("firstname"));
				user.setLastName(rs.getString("lastname"));
				user.setEmail(rs.getString("email"));
				user.setPassword(rs.getString("password"));
				user.setAccessLevel(rs.getInt("accesslevel"));
				user.setStatus(rs.getString("status"));
				user.setToken(rs.getString("token"));
				user.setStartDate(rs.getDate("startDate"));
				user.setSecurityQuestion(rs.getString("securityQuestion"));
				user.setAnswer(rs.getString("answer"));
			}
			
		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}

		return user;
		
	}
	
	/**
	 * Get a record by username and password from Users table
	 * @param username
	 * @param password
	 * @param all True get the user with all status, FALSE only get approved user
	 * @return
	 */
//	public User getUser(String username, String password, boolean all)
//	{
//		String query = "select * from users where username= ? and password = ? ";
//		
//		if ( all == false ) {
//			query += " and status = '"+GlobalCONST.APPROVED +"'";
//		}
//		
//		List paramList = new ArrayList();
//		paramList.add(username);
//		paramList.add(password);
//				
//		ResultSet rs = null;
//		User user = new User();
//
//		try
//		{
//			rs = qm.fetchList(query, paramList);
//			
//			
//			if ( ! rs.first() )  //no record found
//			{
//				System.out.println(query);
//				System.out.println("no record found");
//				return null;
//			}
//			else 
//			{								
//				user.setId(rs.getInt("id"));
//				user.setUsername(rs.getString("username"));
//				user.setFirstName(rs.getString("firstname"));
//				user.setLastName(rs.getString("lastname"));
//				user.setEmail(rs.getString("email"));
//				user.setPassword(rs.getString("password"));
//				user.setAccessLevel(rs.getInt("accesslevel"));
//				user.setStatus(rs.getString("status"));
//			}
//			
//		}
//		catch (SQLException e)
//		{
//			e.printStackTrace();
//		}
//
//		return user;
//	}
	
	/**
	 * Get a record by username from users table
	 * @param username
	 * @return User
	 */
//	public User getUser(String username)
//	{
//		String query = "select * from users where username= ?";
//		
//		List paramList = new ArrayList();
//		paramList.add(username);
//				
//		ResultSet rs = null;
//		User user = new User();
//
//		try
//		{
//			rs = qm.fetchList(query, paramList);
//			
//			if ( ! rs.first() ) 
//			{
//				return null;
//			} else {
//				user.setId(rs.getInt("id"));
//				user.setUsername(rs.getString("username"));
//				user.setFirstName(rs.getString("firstname"));
//				user.setLastName(rs.getString("lastname"));
//				user.setEmail(rs.getString("email"));
//				user.setPassword(rs.getString("password"));
//				user.setAccessLevel(rs.getInt("accesslevel"));
//				user.setStatus(rs.getString("status"));
//			}
//			
//		}
//		catch (SQLException e)
//		{
//			e.printStackTrace();
//		}
//
//		return user;		
//		
//	}
	


	
//	public void updateUser(User user)
//	{
//		String query = "update users set firstname=?, lastname=?, password=? where id=?";
//		
//		List paramList = new ArrayList();
//		paramList.add(user.getFirstName());
//		paramList.add(user.getLastName());
//		paramList.add(user.getPassword());
//		paramList.add(user.getId());
//		
//		qm.executePreparedQuery(query, paramList);
//	}
	
//	public void updateUserStatus(int userId, String status)
//	{
//		String query = "update users set status=? where id = ?";
//		
//		List paramList = new ArrayList();
//		paramList.add(status);
//		paramList.add(userId);
//		qm.executePreparedQuery(query, paramList);
//	}
	
//	public void deleteUser(int userId)
//	{
//		String query = "delete from users where id = ?";
//		
//		List paramList = new ArrayList();
//		paramList.add(userId);
//		
//		qm.executePreparedQuery(query, paramList);		
//	}

	
}
